﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Configuration;
using System.Data;
using System.Reflection;
using System.Collections;

namespace Caixa.DB
{
	public class DAHelper : IDisposable
	{
		#region " Variáveis "

		private string _connectionString;
		private string _providerName;
		private DbProviderFactory _factory;
		private DbConnection _connection;

		#endregion

		#region " Constructor "

		public DAHelper()
		{

		}

		#endregion

		#region " Propriedades "

		private DbProviderFactory Factory
		{
			get
			{
				if (_factory == null)
				{
					_factory = DbProviderFactories.GetFactory(this.ProviderName);
					if (_factory == null)
						throw new Exception("Factory não criado pois o ProviderName não foi encontrado!!");
				}
				return _factory;
			}
		}

		public string ProviderName
		{
			get
			{
				if (string.IsNullOrEmpty(_providerName))
				{
					try
					{
						_providerName = ConfigurationManager.ConnectionStrings["Conexao"].ProviderName;
					}
					catch (Exception e)
					{
						throw new Exception("ProviderName não foi encontrado!!", e);
					}
				}
				return _providerName;
			}

			set
			{
				_providerName = value;
			}
		}

		public string ConnectionString
		{
			get
			{
				if (string.IsNullOrEmpty(_connectionString))
				{
					try
					{
						_connectionString = ConfigurationManager.ConnectionStrings["Conexao"].ConnectionString;
						this.ProviderName = ConfigurationManager.ConnectionStrings["Conexao"].ProviderName;
					}
					catch (Exception e)
					{
						throw new Exception("ConnectionString Conexao não foi encontrada!!", e);
					}
				}

				return _connectionString;
			}

			set
			{
				_connectionString = value;
			}
		}

		public DbConnection Connection
		{
			get
			{
				if (_connection == null)
					_connection = GetNewConnection();

				return _connection;
			}
		}

		#endregion

		#region " Métodos "

		#region " Management "

		public static M ToModel<M>(IEnumerator data)
		{
			M obj = default(M);

			if (data.MoveNext())
			{
				DbDataRecord record = (DbDataRecord)data.Current;
				obj = ToModel<M>(record);
			}

			return obj;
		}

		private static List<M> ToModelList<M>(IEnumerator data)
		{
			List<M> list = new List<M>();

			while (data.MoveNext())
			{
				DbDataRecord record = (DbDataRecord)data.Current;
				list.Add(ToModel<M>(record));
			}

			return list;
		}

		public static M ToModel<M>(DbDataRecord record)
		{
			M obj = Activator.CreateInstance<M>();
			Type type = obj.GetType();
			PropertyInfo[] properts = type.GetProperties();

			Type fieldsType = type.GetNestedType("Fields");
			FieldInfo[] fields = fieldsType.GetFields();

			foreach (FieldInfo field in fields)
			{
				PropertyInfo property = properts.FirstOrDefault<PropertyInfo>(delegate(PropertyInfo p)
				{
					return p.Name.ToUpper() == field.Name.ToUpper();
				});
				if (property != null)
				{
					int ordinal = record.GetOrdinal(string.Format("{0}", field.GetValue(obj)));
					if (!record.IsDBNull(ordinal))
					{
						if (property.PropertyType.IsEnum)
							property.SetValue(obj, Convert.ChangeType(Enum.ToObject(property.PropertyType, record.GetValue(ordinal)), property.PropertyType), null);
						else
							property.SetValue(obj, Convert.ChangeType(record.GetValue(ordinal), property.PropertyType), null);
					}
				}
			}

			return obj;
		}

		private bool OpenConnection()
		{
			bool success = false;

			try
			{
				if (this.Connection != null)
					this.Connection.Open();
				success = true;
			}
			catch (Exception ex)
			{
				throw new Exception("Ocorreu um erro na tentativa de abrir a conexão atual.", ex);
			}

			return success;
		}

		private bool CloseConnection()
		{
			bool success = false;

			try
			{
				if (this._connection != null)
					this._connection.Close();
				success = true;
			}
			catch (Exception ex)
			{
				throw new Exception("Ocorreu um erro na tentativa de fechar a conexão atual.", ex);
			}

			return success;
		}

		public DbConnection GetNewConnection()
		{
			return GetNewConnection(this.ConnectionString, this.ProviderName);
		}

		public DbConnection GetNewConnection(string connectionString, string providerName)
		{
			this.ConnectionString = connectionString;
			this.ProviderName = providerName;
			DbConnection retCon = null;
			if (!string.IsNullOrEmpty(connectionString))
			{

				retCon = Factory.CreateConnection();
				retCon.ConnectionString = this.ConnectionString;
				try
				{
					retCon.Open();
				}
				catch (System.Exception ex)
				{
					throw new Exception("Ocorreu um erro ao criar a conexão como banco de dados.", ex);
				}
				finally
				{
					retCon.Close();
				}
			}

			return retCon;
		}

		private DbCommand GetCommand(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			return GetCommand(commandText, parms, CommandType.Text, dbTransaction);
		}

		private DbCommand GetCommand(string commandText, DbParameter[] parms, CommandType commandType, DbTransaction dbTransaction)
		{
			DbCommand Cmd;

			Cmd = Factory.CreateCommand();
			Cmd.CommandText = commandText;

			if (dbTransaction == null)
				Cmd.Connection = this.Connection;
			else
			{
				Cmd.Connection = dbTransaction.Connection;
				Cmd.Transaction = dbTransaction;
			}

			Cmd.CommandType = CommandType.Text;

			if (parms != null)
				Cmd.Parameters.AddRange(parms);

			return Cmd;
		}

		private DbDataAdapter GetDataAdapter(DbCommand cmd)
		{
			DbDataAdapter Da = Factory.CreateDataAdapter();
			Da.SelectCommand = cmd;

			return Da;
		}

		public DbParameter GetParameter(string name, object value)
		{
			return GetParameter(name, value, null, null, null);
		}

		public DbParameter GetParameter(string name, object value, DbType type)
		{
			return GetParameter(name, value, type, null, null);
		}

		public DbParameter GetParameter(string name, object value, DbType type, int size)
		{
			return GetParameter(name, value, type, size, null);
		}

		public DbParameter GetParameter(string name, object value, DbType? type, int? size, ParameterDirection? direction)
		{
			DbParameter param = Factory.CreateParameter();
			param.ParameterName = name;
			param.Value = value;
			if (type != null)
				param.DbType = (DbType)type;
			if (size != null)
				param.Size = (int)size;
			if (direction != null)
				param.Direction = (ParameterDirection)direction;

			return param;
		}

		#endregion

		#region " SQL Command Text"

		public DbDataReader SelectSqlDr(string commandText)
		{
			return this.SelectSqlDr(commandText, new DbParameter[] { });
		}

		public DbDataReader SelectSqlDr(string commandText, DbParameter[] parms)
		{
			return this.SelectSqlDr(commandText, parms, null);
		}

		public DbDataReader SelectSqlDr(string commandText, DbTransaction dbTransaction)
		{
			return this.SelectSqlDr(commandText, null, dbTransaction);
		}

		public DbDataReader SelectSqlDr(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DbDataReader retDataReader;

			DbCommand Cmd = GetCommand(commandText, parms, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				retDataReader = Cmd.ExecuteReader();

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar o comando SQL no banco de dados.", e);
			}

			return retDataReader;
		}

		public DataSet SelectSqlDs(string commandText)
		{
			return this.SelectSqlDs(commandText, new DbParameter[] { });
		}

		public DataSet SelectSqlDs(string commandText, DbParameter[] parms)
		{
			return this.SelectSqlDs(commandText, parms, null);
		}

		public DataSet SelectSqlDs(string commandText, DbTransaction dbTransaction)
		{
			return this.SelectSqlDs(commandText, null, dbTransaction);
		}

		public DataSet SelectSqlDs(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DataSet retDataSet = new DataSet();
			DbCommand Cmd = GetCommand(commandText, parms, dbTransaction);
			DbDataAdapter Da = GetDataAdapter(Cmd);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Da.Fill(retDataSet);

				Cmd.Parameters.Clear();
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retDataSet;
		}

		public DataTable SelectSqlDt(string commandText)
		{
			return this.SelectSqlDt(commandText, new DbParameter[] { });
		}

		public DataTable SelectSqlDt(string commandText, DbParameter[] parms)
		{
			return this.SelectSqlDt(commandText, parms, null);
		}

		public DataTable SelectSqlDt(string commandText, DbTransaction dbTransaction)
		{
			return this.SelectSqlDt(commandText, null, dbTransaction);
		}

		public DataTable SelectSqlDt(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DataTable retDataTable = new DataTable();
			DbCommand Cmd = GetCommand(commandText, parms, dbTransaction);
			DbDataAdapter Da = GetDataAdapter(Cmd);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Da.Fill(retDataTable);

				Cmd.Parameters.Clear();
			}
			catch (Exception ex)
			{
				throw new Exception("Erro em SelectSqlDt(string commandText, SqlParameter[] parms, SqlTransaction sqlTransaction)", ex);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retDataTable;
		}

		public Object SelectSqlSc(string commandText)
		{
			return this.SelectSqlSc(commandText, new DbParameter[] { });
		}

		public Object SelectSqlSc(string commandText, DbParameter[] parms)
		{
			return this.SelectSqlSc(commandText, parms, null);
		}

		public Object SelectSqlSc(string commandText, DbTransaction dbTransaction)
		{
			return this.SelectSqlSc(commandText, null, dbTransaction);
		}

		public Object SelectSqlSc(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			Object retObject;
			DbCommand Cmd = GetCommand(commandText, parms, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				retObject = Cmd.ExecuteScalar();

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a consulta no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retObject;
		}

		public bool ExecuteSql(string commandText)
		{
			return this.ExecuteSql(commandText, new DbParameter[] { });
		}

		public bool ExecuteSql(string commandText, DbTransaction dbTransaction)
		{
			return this.ExecuteSql(commandText, null, dbTransaction);
		}

		public bool ExecuteSql(string commandText, DbParameter[] parms)
		{
			return this.ExecuteSql(commandText, parms, null);
		}

		public bool ExecuteSql(string commandText, DbParameter[] parms, DbTransaction dbTransaction)
		{
			bool success = false;

			DbCommand Cmd = GetCommand(commandText, parms, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Cmd.ExecuteNonQuery();

				Cmd.Parameters.Clear();

				success = true;
			}
			catch (Exception ex)
			{
				throw new Exception("Ocorreu um erro ao executar o comando no banco de dados.", ex);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return success;
		}

		#endregion

		#region " SQL Stored Procedures "

		public DbDataReader SelectSpDr(string nameStoredProcedure)
		{
			return this.SelectSpDr(nameStoredProcedure, new DbParameter[] { });
		}

		public DbDataReader SelectSpDr(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.SelectSpDr(nameStoredProcedure, parms, null);
		}

		public DbDataReader SelectSpDr(string nameStoredProcedure, DbTransaction dbTransaction)
		{
			return this.SelectSpDr(nameStoredProcedure, null, dbTransaction);
		}

		public DbDataReader SelectSpDr(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DbDataReader retDataReader;

			try
			{
				DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);

				if (dbTransaction == null)
				{
					if (this.Connection != null)
					{
						if ((this.Connection.State != ConnectionState.Closed))
							this.CloseConnection();
					}
				}

				if (dbTransaction == null)
					this.OpenConnection();

				retDataReader = Cmd.ExecuteReader();

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}

			return retDataReader;
		}

		public DataSet SelectSpDs(string nameStoredProcedure)
		{
			return this.SelectSpDs(nameStoredProcedure, new DbParameter[] { });
		}

		public DataSet SelectSpDs(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.SelectSpDs(nameStoredProcedure, parms, null);
		}

		public DataSet SelectSpDs(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DataSet retDataSet = new DataSet();

			DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);

			DbDataAdapter Da = GetDataAdapter(Cmd);
			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Da.Fill(retDataSet);

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retDataSet;
		}

		public DataTable SelectSpDt(string nameStoredProcedure)
		{
			return this.SelectSpDt(nameStoredProcedure, new DbParameter[] { });
		}

		public DataTable SelectSpDt(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.SelectSpDt(nameStoredProcedure, parms, null);
		}

		public DataTable SelectSpDt(string nameStoredProcedure, DbTransaction dbTransaction)
		{
			return this.SelectSpDt(nameStoredProcedure, null, dbTransaction);
		}

		public DataTable SelectSpDt(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			DataTable retDataTable = new DataTable();
			DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);
			DbDataAdapter Da = GetDataAdapter(Cmd);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Da.Fill(retDataTable);

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retDataTable;
		}

		public Object SelectSpSc(string nameStoredProcedure)
		{
			return this.SelectSpSc(nameStoredProcedure, new DbParameter[] { });
		}

		public Object SelectSpSc(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.SelectSpSc(nameStoredProcedure, parms, null);
		}

		public Object SelectSpSc(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			Object retObject;
			DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);

			try
			{
				if ((this.Connection.State != ConnectionState.Closed) && (dbTransaction == null))
					this.CloseConnection();

				if (dbTransaction == null)
					this.OpenConnection();

				retObject = Cmd.ExecuteScalar();

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retObject;
		}

		public bool ExecuteSp(string nameStoredProcedure)
		{
			return this.ExecuteSp(nameStoredProcedure, new DbParameter[] { });
		}

		public bool ExecuteSp(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.ExecuteSp(nameStoredProcedure, parms, null);
		}

		public bool ExecuteSp(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			bool success = false;

			DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Cmd.ExecuteNonQuery();

				Cmd.Parameters.Clear();
				success = true;
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return success;
		}

		public object ExecuteSpSc(string nameStoredProcedure)
		{
			return this.ExecuteSpSc(nameStoredProcedure, new DbParameter[] { });
		}

		public object ExecuteSpSc(string nameStoredProcedure, DbParameter[] parms)
		{
			return this.ExecuteSpSc(nameStoredProcedure, parms, null);
		}

		public object ExecuteSpSc(string nameStoredProcedure, DbTransaction dbTransaction)
		{
			return this.ExecuteSpSc(nameStoredProcedure, null, dbTransaction);
		}

		public object ExecuteSpSc(string nameStoredProcedure, DbParameter[] parms, DbTransaction dbTransaction)
		{
			object retObject;
			DbCommand Cmd = GetCommand(nameStoredProcedure, parms, CommandType.StoredProcedure, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				retObject = Cmd.ExecuteScalar();

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao executar a stored procedure no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return retObject;
		}

		#endregion

		#region " DDL Methods "

		#region " Insert "

		public int InsertSql(string tableName, DbParameter[] fields)
		{
			return this.InsertSql(tableName, fields, null);
		}

		public int InsertSql(string tableName, DbParameter[] fields, DbTransaction dbTransaction)
		{
			int newID = 0;
			string cmdSql = SQLHelper.CreateInsertCommand(tableName, fields);
			DbCommand Cmd = GetCommand(cmdSql, fields, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				object returnedValue = Cmd.ExecuteNonQuery();

				Cmd.CommandText = SQLHelper.SQL_IDENTITY;
				returnedValue = Cmd.ExecuteScalar();

				if (returnedValue != DBNull.Value)
					newID = Convert.ToInt32(returnedValue);

				Cmd.Parameters.Clear();
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao tentar inserir um registro no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return newID;
		}

		#endregion

		#region " Update "

		public bool UpdateSql(string tableName, DbParameter[] fields, string whereFilter)
		{
			return this.UpdateSql(tableName, fields, whereFilter, null);
		}

		public bool UpdateSql(string tableName, DbParameter[] fields, string whereFilter, DbTransaction dbTransaction)
		{
			bool success = false;

			string cmdSql = SQLHelper.CreateUpdateCommand(tableName, fields, whereFilter);
			DbCommand Cmd = GetCommand(cmdSql, fields, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Cmd.ExecuteNonQuery();

				Cmd.Parameters.Clear();

				success = true;
			}
			catch (Exception e)
			{
				success = false;
				throw new Exception("Ocorreu um erro ao atualizar um registro no banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return success;
		}

		#endregion

		#region " Delete "

		public bool DeleteSql(string tableName, string whereFilter)
		{
			return this.DeleteSql(tableName, whereFilter, null);
		}

		public bool DeleteSql(string tableName, string whereFilter, DbTransaction dbTransaction)
		{
			bool success = false;
			string cmdSql = SQLHelper.CreateDeleteCommand(tableName, whereFilter);
			DbCommand Cmd = GetCommand(cmdSql, null, dbTransaction);

			try
			{
				if (dbTransaction == null)
					this.OpenConnection();

				Cmd.ExecuteNonQuery();
				success = true;
			}
			catch (Exception e)
			{
				throw new Exception("Ocorreu um erro ao excluir um registro do banco de dados.", e);
			}
			finally
			{
				if (dbTransaction == null)
					this.CloseConnection();
			}

			return success;
		}

		#endregion

		#endregion

		#endregion

		#region " Dispose "

		public void Dispose()
		{
			this.CloseConnection();
			GC.SuppressFinalize(this);
		}

		#endregion
	}
}
